package jsyf.oa.dao.crm;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.com.util.DateUtils;
import jsyf.oa.entity.crm.ExhibitionEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.text.DateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by WHH-PC on 2017/12/24.
 */
@Repository
public class ExhibitionDao extends BaseDao {

    public ExhibitionDao() {
        super.iBaseEntity = new ExhibitionEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, ExhibitionEntity.class);
    }

    public List<JSONObject> getRegion2Manager() {
        String strSql = "SELECT * FROM V_REGION_MANAGER";
        return queryForJsonList(strSql);
    }

    public List<JSONObject> getExhibitionList() {
        String strSql = "SELECT COMPANY_ID,EXHIBITION_DATE,TYPE FROM T_CRM_EXHIBITION";
        return queryForJsonList(strSql);
    }

    public List<JSONObject> getRegionByUserId(String userId) {
        StringBuffer sb = new StringBuffer();
//        sb.append("select A.id,A.name,A.PARENT_ID parentId from T_SYS_REGION A ");
//        sb.append("LEFT JOIN T_SYS_USER_2_REGION B ON A.ID=B.REGION_ID ");
//        sb.append("WHERE A.DEL_FLAG=0 AND B.USER_ID='");
//        sb.append(userId);
//        sb.append("' ORDER BY A.ID");

        sb.append("SELECT * FROM ( ");
        sb.append("        SELECT A.ID,A.NAME,A.PARENT_ID PARENTID FROM T_SYS_REGION A");
        sb.append("        LEFT JOIN T_SYS_USER_2_REGION B ON A.ID=B.REGION_ID WHERE A.DEL_FLAG=0");
        sb.append("        AND B.USER_ID='" + userId + "'");
        sb.append("        UNION ALL");
        sb.append("        SELECT ID,NAME,PARENTID FROM V_BIG_CUSTOMER_REGION WHERE USERID='" + userId + "'");
//        sb.append("        union all");
//        sb.append("        select id,name,parentId from v_big_customer_region where managerId='" + userId + "'");
        sb.append(") A ORDER BY A.PARENTID,A.ID");
        return queryForJsonList(sb.toString());
    }

    public Map<String, JSONObject> getRegion2ManagerMap() {
        Map<String, JSONObject> map = new HashMap<>();
        getRegion2Manager().forEach(e -> {
            map.put(e.get("REGIONID").toString(), e);
        });

        return map;
    }

    public List<JSONObject> getExhibitionListByCompany(String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM T_CRM_EXHIBITION WHERE COMPANY_ID='" + companyId + "' ");
        sb.append("or '201712161613432EAF27DBCCB7D0E6CA'='" + companyId + "' ");
        sb.append("ORDER BY CREATE_TIME DESC ");
        return queryForJsonList(sb.toString());
    }

    public JSONObject getExhibitionDescLimit1() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM T_CRM_EXHIBITION order by CREATE_TIME desc LIMIT 1 ");
        List<JSONObject> jsonObjectList = queryForJsonList(sb.toString());
        if (jsonObjectList != null && jsonObjectList.size() > 0)
            return jsonObjectList.get(0);
        else
            return null;
    }

    public List<JSONObject> getExhibitionById(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM T_CRM_EXHIBITION WHERE id='" + id + "' ");
        return queryForJsonList(sb.toString());
    }

    public List<JSONObject> getExhibitionAims(String exhibitionId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT EXHIBITION_ID exhibitionId,REGION_ID regionId,");
        sb.append("CUSTOM_NUM customNum,DEPOSIT_NUM depositNum from ");
        sb.append("T_CRM_EXHIBITION_AIMS where EXHIBITION_ID='" + exhibitionId + "'");
        return queryForJsonList(sb.toString());
    }

    public Map<String, JSONObject> getExhibitionAimsMap(String exhibitionId) {
        Map<String, JSONObject> map = new HashMap<>();
        getExhibitionAims(exhibitionId).forEach(e -> {
            map.put(e.get("regionId").toString(), e);
        });

        return map;
    }

    public List<JSONObject> getExhibitionCustomList(String exhibitionId) {
        StringBuffer sb = new StringBuffer();
        sb.append("select B.regionId,A.* from T_CRM_EXHIBITION_CUSTOMER_INFO A ");
        sb.append("left join V_REGION_MANAGER B on A.CREATE_USER_ID=B.userId ");
        sb.append("where A.EXHIBITION_ID='" + exhibitionId + "' AND A.TRACK_STATUS=1 AND B.regionId is not null ");
        return queryForJsonList(sb.toString());
    }

    public List<JSONObject> getExhibitionIdByUserId(String userId, Boolean isGroupUsers) {
        StringBuffer sb = new StringBuffer();
        sb.append("select D.ID exhibitionId,D.CREATE_USER_ID,D.NAME,D.EXHIBITION_DATE from T_SYS_USER A  ");
        sb.append("LEFT JOIN  T_SYS_ROLE B ON A.ROLE_ID=B.ID ");
        sb.append("LEFT JOIN T_SYS_DEPART C ON B.DEPART_ID=C.ID ");
        sb.append("LEFT JOIN T_CRM_EXHIBITION D ON C.COMPANY_ID=D.COMPANY_ID ");
        sb.append("WHERE 1=1 AND DATE_FORMAT( D.EXHIBITION_DATE,'%Y-%m-%d')>='" + DateUtils.parse(new Date(), "yyyy-MM-dd") + "' ");
        if (isGroupUsers == false)
            sb.append(" AND A.ID='" + userId + "' ");
        sb.append("ORDER BY D.CREATE_TIME LIMIT 1");
        return queryForJsonList(sb.toString());
    }

    public List<JSONObject> getExihibitionList(String condition) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM V_CRM_EXHIBITION ");
        sbSql.append("WHERE 1=1  " + condition + " ");
        return queryForJsonList(sbSql.toString());
    }

    public Integer getExihibitionListCount(String condition) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("select count(1) c from V_CRM_EXHIBITION a where 1=1");
        sbSql.append(condition.toString());
        return Integer.parseInt(queryForJsonList(sbSql.toString()).get(0).get("c").toString());
    }

    public List<JSONObject> getNomanagerRegion(String userId) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT id FROM T_SYS_REGION WHERE PARENT_ID IN (");
        sbSql.append("        SELECT REGION_ID FROM T_SYS_USER_2_REGION A");
        sbSql.append("        LEFT JOIN V_REGION_MANAGER B ON A.REGION_ID=B.regionId");
        sbSql.append("        WHERE A.USER_ID='" + userId + "' AND B.regionId IS NULL");
        sbSql.append(") AND DEL_FLAG=0");
        return queryForJsonList(sbSql.toString());
    }

    public JSONObject getBigCusDepart(String companyId) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("select * from  T_SYS_REGION where name='大客户部'");
        sbSql.append(" and company_id='" + companyId + "'");
        return queryForJsonObject(sbSql.toString());
    }

    public List<JSONObject> getBigCusRegion(String companyId) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("select A.REGION_ID regionId,E.PARENT_ID parentId,E.NAME regionName ");
        sbSql.append("from T_SYS_USER_2_REGION A ");
        sbSql.append("LEFT JOIN T_SYS_USER B ON A.USER_ID=B.ID ");
        sbSql.append("LEFT JOIN T_SYS_ROLE C ON C.ID=B.ROLE_ID ");
        sbSql.append("LEFT JOIN T_SYS_ROLE_NAME D ON C.ROLE_NAME_ID=D.ID ");
        sbSql.append("LEFT JOIN T_SYS_REGION  E ON A.REGION_ID=E.ID ");
        sbSql.append("where E.COMPANY_ID='" + companyId + "' AND D.NAME='大客户总监' ");
        return queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getCompanyExhibitionCount() {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT companyId,companyName,managerName,salerManagerName, ");
        sbSql.append("(SELECT COUNT(*) from t_crm_exhibition where company_id=a.companyId) as exhibitionNumber ");
        sbSql.append("from v_sys_company as a WHERE companyParentId!='0' and companyDelFlag='0' ");
        sbSql.append("AND companyName not like '%大客户%' ");
        return queryForJsonList(sbSql.toString());
    }
}
